package com.situ.storehouse.dao;


import java.util.List;

import com.situ.storehouse.entity.Bill;
import com.situ.storehouse.util.Dbutil;
import com.situ.storehouse.vo.BillVO;

public class BillDao {

	public void add(Bill bill) {
		String sql = "insert into bill(wares_id,storedate,time,creatby,company_name,deliveryman_name,num,status) values(?,?,?,?,?,?,?,?)";
		Dbutil.executeDML(sql,bill.getWaresId(),bill.getStoredate(),bill.getTime(),bill.getCreatby(), bill.getCompanyName(),bill.getDeliverymanName()
				,bill.getNum(),bill.getStatus());
	}

	public void delete(Integer id) {
		String sql = "delete from bill where id=?";
		Dbutil.executeDML(sql, id);
	}

	public void update(Bill bill) {
		String sql = "update bill set wares_id=?,storedate=?,time=?,creatby=?,company_name=?,deliveryman_name=?,num=?,status=? where id=?";
		Dbutil.executeDML(sql,bill.getWaresId(),bill.getStoredate(),bill.getTime(),bill.getCreatby(),bill.getCompanyName(),bill.getDeliverymanName()
				,bill.getNum(),bill.getStatus(),bill.getId());
	}

	public Bill select(Integer id) {
		String sql = "select * from bill where id=?";
		List<Bill> list = Dbutil.executeDQL(sql,Bill.class, id);
		return list.get(0);
	}
	
	public List<BillVO> select(Integer page,Integer limit,String keyword,String maindate) {
		String sql=null;
		if(keyword==null || keyword.equals("")) {
			if(maindate==null||maindate.equals("")) {
				sql = "select bill.*,wares.wares_name,wares.factory,wares.model,wares.specifications from bill inner join wares on bill.wares_id=wares.id limit ?,?";
			}else {
				sql = "select bill.*,wares.wares_name,wares.factory,wares.model,wares.specifications from bill inner join wares on bill.wares_id=wares.id"
						+ " where storedate='"+maindate+"' limit ?,?";
			}
		}else {
			if(maindate!=null && !"".equals(maindate) ) {
				sql = "select bill.*,wares.wares_name,wares.factory,wares.model,wares.specifications from bill inner join wares on bill.wares_id=wares.id "
						+ " where instr(wares_name,'"+keyword+"')>0 and storedate='"+maindate+"' limit ?,?";
			}else {
				sql = "select bill.*,wares.wares_name,wares.factory,wares.model,wares.specifications from bill inner join wares on bill.wares_id=wares.id "
						+ " where instr(wares_name,'"+keyword+"')>0  limit ?,?";
			}
		}
		return Dbutil.executeDQL(sql, BillVO.class,page,limit);
	}

	public Long count(String keyword,String maindate) {
		String sql=null;
		if(keyword==null || keyword.equals("")) {
			if(maindate==null||maindate.equals("")) {
				sql = "select count(1) from bill inner join wares on bill.wares_id=wares.id";
			}else {
				sql = "select count(1) from bill inner join wares on bill.wares_id=wares.id"
						+ " where storedate='"+maindate+"'  ";
			}
		}else {
			if(maindate!=null && !"".equals(maindate) ) {
				sql = "select count(1) from bill inner join wares on bill.wares_id=wares.id "
						+ " where instr(wares.wares_name,'"+keyword+"')>0 and storedate='"+maindate+"' ";
			}else {
				sql = "select count(1) from bill inner join wares on bill.wares_id=wares.id "
						+ " where instr(wares.wares_name,'"+keyword+"')";
			}
		}
		return Dbutil.executeCount(sql);
		
	}

	
	

	
}
